﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QLBenhVien
{
    class DAOUser
    {
        static public DataTable getUser(string username, string password)
        {
            string sql = "select * from users where username = @username and password = @password";      
            DataTable dt = new DataTable();            
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.SelectCommand.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
                da.SelectCommand.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }
        public static DataTable getAllUser()
        {
            string sql = "select * from users";
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }
        public static DataTable getUserWithName(string txt)
        {
            string sql = "select * from users where fullname like @name";
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = "%"+txt+"%";
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }
        public static void DelUser(int id)
        {
            string sql = "delete from users where id=@id";
            try
            {
                SqlConnection ketnoi = new SqlConnection(DB_Provider.conn_str);
                ketnoi.Open();
                SqlCommand cmd = new SqlCommand(sql, ketnoi);
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
                ketnoi.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public static bool getUserWithUName(string txt)
        {
            string sql = "select count(*) from users where username = @name";
            SqlConnection ketnoi = new SqlConnection(DB_Provider.conn_str);
            ketnoi.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, ketnoi);
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = txt;
                int result = (int)cmd.ExecuteScalar();
                ketnoi.Close();
                if (result == 1)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public static void AddUser(DBOUser ur)
        {
            string sql = "insert into users values(@username,@password,@usertype,@fullname)";
            SqlConnection ketnoi = new SqlConnection(DB_Provider.conn_str);
            ketnoi.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, ketnoi);
                cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = ur.username;
                cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = ur.password;
                cmd.Parameters.Add("@usertype", SqlDbType.Int).Value = ur.usertype;
                cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = ur.fullname;
                cmd.ExecuteNonQuery();
                ketnoi.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public static void UpdateFullUsr(DBOUser u)
        {
            string sql = "update users set username=@username, password=@password, usertype=@usertype, fullname=@fullname where id=@id ";
            SqlConnection ketnoi = new SqlConnection(DB_Provider.conn_str);
            ketnoi.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, ketnoi);
                cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = u.username;
                cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = u.password;
                cmd.Parameters.Add("@usertype", SqlDbType.Int).Value = u.usertype;
                cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = u.fullname;
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = u.id;
                cmd.ExecuteNonQuery();
                ketnoi.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public static void UpdateNonPassUsr(DBOUser u)
        {
            string sql = "update users set username=@username, usertype=@usertype, fullname=@fullname where id=@id ";
            SqlConnection ketnoi = new SqlConnection(DB_Provider.conn_str);
            ketnoi.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, ketnoi);
                cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = u.username;
                cmd.Parameters.Add("@usertype", SqlDbType.Int).Value = u.usertype;
                cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = u.fullname;
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = u.id;
                cmd.ExecuteNonQuery();
                ketnoi.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
